Some conventional database systems offer Structured Query Language (SQL) diagnosis and management products. These conventional products aim to help customers complete tasks including understanding and resolving performance issues, diagnosing faults in the product, and running tests to see how their system will behave after performing some change. Conventionally, SQL management products have not tested user SQL statements within server code. Since the SQL statements are not executed within server code, there is a lower certainty and/or confidence in advice based on the resulting data. The certainty and/or confidence is negatively impacted by the inability to execute the test code in a realistic environment and to produce reproducible, verifiable statistics. Indeed, conventional approaches may be unable to prove their advice. Running the SQL statement externally will create issues, including causing contention with other database activity due to acquisition of row locks, modifying the database state which will cause other database sessions to do extra work to see a version of the database before these changes were made, and mixing the statistics collected for normal SQL executions run by the application with those collected for testing.
Several challenges have frustrated attempts to test user SQL statements within server code. For example, it has been difficult, if possible at all, to collect reliably certain information about SQL statements to be tested. A set of SQL statements may be referred to as “an SQL” and/or as “SQLs”. Conventional approaches to collecting statistics for a single user's activity involve tracing, which can negatively impact performance itself, or attempting to capture performance information from the system's dynamic performance views, which can be unreliable and also can mix user statistics with the test statistics when cursors are shared. Another challenge is reproducing the original execution environment when test-executing the SQL. This is challenging enough when running the SQL on the same system, but becomes all the more challenging when executing on a secondary test system. The environment is a very complex and heterogeneous entity. Therefore, defining, capturing, transporting, and reproducing an environment is challenging. Additionally, it has been difficult, if even possible, to execute SQLs without causing a noticeable impact on the system on which the testing occurs. Noticeable impacts may be caused by acquiring row locks, modifying user data when running data manipulation language statements, which will impact those statements that come later, and so on. Tools that execute user SQLs in a way that can impact a production workload will be less useful than tools that prevent this harmful interaction. Like a set of SQL statements may be referred to as “an SQL”, a set of data manipulation language (DML) statements may be referred to as “a DML”.
When executing a single SQL statement, conventional systems are challenged to force the SQL to complete or abort within a specific time limit. While not critical for normal user executions of SQL statements being run by an application where all SQLs are expected to run to completion no matter how long it takes, performance and diagnostic tools that are built into the database cannot afford to run SQL statements for an unspecified amount of time because this would cause noticeable harm to the system. Therefore performance and diagnostic tools determine how much time the SQL statement should be allowed to run for and then terminate the execution once the execution reaches this time limit.
Another challenge for conventional systems has been finding an effective way to measure and compare the performance of different execution plans for the same user SQL. This involves reproducing the set of execution plans of interest, executing them, capturing relevant performance metrics for those executions only, and choosing an appropriate set of metrics that can be used to judge the goodness of the different plans and compare them to each other. Yet another challenge for conventional systems has been discovering an approach to manage the execution of multiple plans for the same user SQL in a scheme that addresses fairness in how resources are split between plans and accuracy in how the plans are compared and evaluated. Time resources need to be used carefully so that all plans will complete in a reasonable amount of time with a useful result that facilitates identifying which plan has the best performance. In some cases, depending on which plans complete and which do not, comparisons may or may not be valid. Example systems account for the (in)validity of comparisons.
Analyzing the results of user SQL statements is also a challenge. Typical interfaces for running SQLs require the application to know in advance the format of the results of the SQL and to define locations in memory to place results. Doing this in a generic fashion is not trivial but it is useful for tools seeking to analyze the results of the SQL execution to, for example, assure that all execution plans for the same query return the same data. Additional challenges are added because some of the information that an analysis tool would like to examine is only available inside the database while the SQL is executing.